# utils.py
from django.db.models import Q
import pandas as pd
from django.shortcuts import render, redirect
from django.http import HttpResponse
from .forms import UploadFileForm
from jiaowu_xueyuan.models import Xueyuan_zongbiao
from KeHu.models import KeHu, KeHu_zhaungtai
from django.contrib.auth.models import User
from TuiGuang.models import tg_qudao
from jiaowu_xueyuan.models import Xueyuan_Zilao
from datetime import datetime
from dateutil.relativedelta import relativedelta
from django.views.generic import ListView, DeleteView, CreateView, DetailView
from django.contrib.auth.mixins import LoginRequiredMixin
import time
import math
import sys
import traceback
from django.contrib.auth.decorators import login_required
from jiaowu_xueyuan.models import Banji

def build_search_query(model, search_params):
    """
    根据搜索参数构建查询并返回查询集。

    :param model: Django 模型类
    :param search_params: 包含搜索参数的字典
    :return: 查询集（QuerySet）
    """
    query = Q()
    for key, value in search_params.items():
        if value:
            query &= Q(**{key + '__icontains': value})

    if query:
        return model.objects.filter(query)
    else:
        return model.objects.none()  # 或者你可以返回所有对象 model.objects.all()，根据你的需求决定

def get_kehulist_search_params(request):
    """
    从请求中获取搜索参数。
    :param request: HttpRequest 对象
    :return: 包含搜索参数的字典
    """
    return {
        'admin_user': request.GET.get('admin_user', ''),
        'username': request.GET.get('username', ''),
        'tel': request.GET.get('tel', ''),
        'tg_qudao': request.GET.get('tg_qudao', ''),
        'gonghai_choices': request.GET.get('gonghai_choices', ''),
        # ... 其他搜索字段
    }

@login_required
def upload_excel_file(request,pk):
    context = {'form': UploadFileForm()}  # 初始化 context 字典

    if request.method == 'GET':
        # 检查 URL 中是否有参数 'pljb'
        if 'pljb' in request.GET:
            pljb = request.GET.get('pljb')
            context['pljb'] = pljb

    elif request.method == 'POST':
        form = UploadFileForm(request.POST, request.FILES)
        if form.is_valid():
            if pk == 1:  # 1---客户导入
                file = request.FILES['file']
                # 读取 Excel 文件
                df = pd.read_excel(file)
                success_count = 0
                error_messages = []
                gslssbad = set()
                for _, row in df.iterrows():
                    try:
                        # 检查是否已存在
                        youshouji = KeHu.objects.filter(tel=int(row['学员手机号'])).exists()
                        if youshouji:
                            continue
                        # 创建新客户实例
                        xueyuanxingming = row['学员姓名'] if row['学员姓名'] else str(row['学员id'])

                        gslss = 1 if row['gslss'] not in [None, ''] else math.trunc(row['gslss'])
                        if_gonghais = 1 if row['gslss'] not in [None, ''] else 0

                        admin_users = User.objects.get(id=gslss)
                        add_admin_users = User.objects.get(id=gslss)
                        tg_channels = tg_qudao.objects.get(id=row['学员来源'])

                        instance_kehu = KeHu(
                            id=int(row['学员id']),
                            username=xueyuanxingming,
                            name=row['学员姓名'],
                            tel=int(row['学员手机号']),
                            wechat=row['学员微信'],
                            qq=row['学员QQ'],
                            beizhu=row['学员最近的一条跟进记录'],
                            zixuntime=row['addtime'],
                            admin_user=admin_users,
                            addtime=row['addtime'],
                            add_admin_user=add_admin_users,
                            tg_qudao=tg_channels,
                            if_gonghai=if_gonghais,
                        )
                        instance_kehu.save()
                        # 创建客户的状态信息
                        instance_kehu_zhuangtai = KeHu_zhaungtai(kehu=instance_kehu)
                        instance_kehu_zhuangtai.save()

                        instance_kehu.save()  # 更新客户的字段值

                        success_count += 1
                    except User.DoesNotExist:
                        error_messages.append(f"用户ID {row['学员id']} 不存在")
                    except tg_qudao.DoesNotExist:
                        error_messages.append(f"推广渠道ID {row['学员来源']} 不存在")
                    except Exception as e:
                        error_messages.append(f"处理记录时发生错误: {str(e)}")
                if success_count > 0:
                    return HttpResponse(f"成功导入 {success_count} 条记录. 错误信息: {'; '.join(error_messages)}", status=200)
                else:
                    return HttpResponse(f"没有记录被导入. 错误信息: {'; '.join(error_messages)}", status=400)
            # 重定向到成功页面或返回成功消息
            elif pk == 2:
                file = request.FILES['file']
                # 读取 Excel 文件
                df = pd.read_excel(file)
                success_count = 0
                error_messages = []
                gslssbad = set()
                for _, row in df.iterrows():
                    try:
                        # 检查是否已存在同号码客户
                        if KeHu.objects.filter(tel=row['手机号码']).exists():
                            continue
                        else:
                            # 创建客户
                            instance_kehu=KeHu(
                                username=row['姓名'],
                                tel=row['手机号码'],
                                wechat=row['微信'],
                                beizhu=row['备注'],
                                )
                            instance_kehu.save()
                            #创建客户对应的状态信息
                            KeHu_zhaungtai.objects.create(
                                kehu=instance_kehu
                            )
                            #获取成交老师对应实例
                            chengjiao_laoshis=User.objects.get(id=row['成交老师'])
                            #创建正式学员
                            instance_Xueyuan_zongbiao = Xueyuan_zongbiao(
                                kehu=instance_kehu,
                                chengjiao_laoshi=chengjiao_laoshis,
                                baomingtime=row['报名时间'],
                                shenfenzheng=row['证件号码'],
                                wenhuachengdu=row['文化程度'],
                                jiguan=row['籍贯'],
                                beizhu=row['备注'],
                                xueyuan_xuehao=row['学号'],
                                xueyuan_kaoshenghao=row['考生号'],
                            )
                            instance_Xueyuan_zongbiao.save()
                            #新建对应资料
                            instance_Xueyuan_Zilao = Xueyuan_Zilao(
                                xueyuan=instance_Xueyuan_zongbiao,
                                xm=row['姓名'],
                                xb=row['性别'],
                                zzmm=row['政治面貌'],
                                zjlx=row['证件类型'],
                                zjhm=row['证件号码'],
                                csnyr=row['出生年月日'],
                                mz=row['民族'],
                                zycc=row['专业层次'],
                                zydm=row['专业代码'],
                                zymc=row['专业名称'],
                                xslx=row['学生类型'],
                                rxcscj=row['入学测试成绩'],
                                zzzt=row['在职状况'],
                                xz=row['学制'],
                                fbqk=row['分布情况'],
                                hyzt=row['婚姻状况'],
                                xfly=row['学费来源'],
                                hkxz=row['户口性质'],
                                jgsf=row['籍贯（省）'],
                                jgs=row['籍贯（地市县）'],
                                hkszd=row['户口所在地'],
                                tel=row['手机号码'],
                                gdtel=row['固定电话'],
                                yx=row['邮箱'],
                                txdz=row['通信地址'],
                                yb=row['邮编'],
                                sfdd=row['是否电大毕业'],
                                yxlcc=row['原学历层次'],
                                ybyxx=row['原毕业学校'],
                                bysj=row['毕业时间（年月日）'],
                                yxk=row['原学科'],
                                yxkml=row['原学科门类'],
                                yxlxxlx=row['原学历学习类型'],
                                xxlbyzszy=row['原学历所学专业'],
                                xxlbyzsbh=row['原学历毕业证书编号'],
                                yxlzmcl=row['原学历证明材料'],
                                zmclbh=row['证明材料编号'],
                                yxlxm=row['原学历姓名'],
                                yxlzjlx=row['原学历证件类型'],
                                yxlzjhm=row['原学历证件号码'],
                            )
                            instance_Xueyuan_Zilao.save()
                            success_count += 1
                    except KeHu.DoesNotExist:
                             error_messages.append(f"用户ID {row['学员id']} 不存在")
                    except KeHu_zhaungtai.DoesNotExist:
                         error_messages.append(f"推广渠道ID {row['学员来源']} 不存在")
                    except Exception as e:
                        error_messages.append(f"处理记录时发生错误: {str(e)}")
                        # 返回响应，包括成功数量和错误信息
                if success_count > 0:
                    return HttpResponse(f"成功导入 {success_count} 条记录. 错误信息: {'; '.join(error_messages)}",
                                        status=200)
                else:
                    print(f"错误信息id: {gslssbad}")
                    return HttpResponse(f"没有记录被导入. 错误信息: {'; '.join(error_messages)}", status=400)

            elif pk == 3:
                file = request.FILES['file']
                # 读取 Excel 文件
                df = pd.read_excel(file)
                success_count = 0
                error_messages = []
                gslssbad = set()
                for _, row in df.iterrows():
                    try:
                        instance_Xueyuan_zongbiao = Xueyuan_zongbiao.objects.get(shenfenzheng=row['证件号码'])

                        # 新建对应资料
                        csdate_value = row['出生年月日']  # 或者可能是其他类型，如 int 或 float
                        # 确保它是字符串
                        if not isinstance(csdate_value, str):
                            csdate_value = str(csdate_value)
                        try:
                            csdate = datetime.fromisoformat(csdate_value)
                        except ValueError as e:
                            error_messages.append(f"出生年月日格式错误: {csdate_value}。行号：{sys._getframe().f_lineno}")
                            continue

                        bydate_value = row['毕业时间']  # 或者可能是其他类型，如 int 或 float
                        # 确保它是字符串
                        if not isinstance(bydate_value, str):
                            bydate_value = str(bydate_value)
                        try:
                            bydate = datetime.fromisoformat(bydate_value)
                        except ValueError as e:
                            error_messages.append(f"毕业年月日格式错误: {bydate_value}。行号：{sys._getframe().f_lineno}|||{success_count}")
                            continue

                        instance_Xueyuan_Zilao = Xueyuan_Zilao(
                            xueyuan=instance_Xueyuan_zongbiao,
                            xm=row['姓名'],
                            xb=row['性别'],
                            zzmm=row['政治面貌'],
                            zjlx=row['证件类型'],
                            zjhm=row['证件号码'],
                            csnyr=csdate,
                            mz=row['民族'],
                            zycc=row['专业层次'],
                            zydm=row['专业代码'],
                            zymc=row['专业名称'],
                            xslx=row['学生类型'],
                            rxcscj=row['入学测试成绩'],
                            zzzt=row['在职状况'],
                            xz=row['学制'],
                            fbqk=row['分布情况'],
                            hyzt=row['婚姻状况'],
                            xfly=row['学费来源'],
                            hkxz=row['户口性质'],
                            jgsf=row['籍贯（省）'],
                            jgs=row['籍贯（地市县）'],
                            hkszd=row['户口所在地'],
                            tel=row['手机号码'],
                            gdtel=row['固定电话'],
                            yx=row['邮箱'],
                            txdz=row['通信地址'],
                            yb=row['邮编'],
                            sfdd=row['是否电大毕业'],
                            yxlcc=row['原学历层次'],
                            ybyxx=row['原毕业学校'],
                            bysj=bydate,
                            yxk=row['原学科'],
                            yxkml=row['原学科门类'],
                            yxlxxlx=row['原学历学习类型'],
                            xxlbyzszy=row['原学历所学专业'],
                            xxlbyzsbh=row['原学历毕业证书编号'],
                            yxlzmcl=row['原学历证明材料'],
                            zmclbh=row['证明材料编号'],
                            yxlxm=row['原学历姓名'],
                            yxlzjlx=row['原学历证件类型'],
                            yxlzjhm=row['原学历证件号码'],
                        )
                        instance_Xueyuan_Zilao.save()
                        instance_Xueyuan_zongbiao.ziliao = instance_Xueyuan_Zilao
                        instance_Xueyuan_zongbiao.save()

                        success_count += 1
                    except KeHu.DoesNotExist:
                        error_messages.append(f"用户ID {row['学员id']} 不存在")
                    except KeHu_zhaungtai.DoesNotExist:
                        error_messages.append(f"推广渠道ID {row['学员来源']} 不存在")
                    except Exception as e:
                        error_messages.append(f"处理记录时发生错误: {str(e)}。行号：{sys._getframe().f_lineno}")
                        # 返回响应，包括成功数量和错误信息
                if success_count > 0:
                    return HttpResponse(f"成功导入 {success_count} 条记录. 错误信息: {'; '.join(error_messages)}",
                                        status=200)
                else:
                    print(f"错误信息id: {gslssbad}")
                    return HttpResponse(f"没有记录被导入. 错误信息: {'; '.join(error_messages)}", status=400)

            elif pk == 4:#通过文件批量导入学员
                file = request.FILES['file']
                # 读取 Excel 文件
                df = pd.read_excel(file)
                success_count = 0
                error_messages = []
                gslssbad = set()
                for _, row in df.iterrows():
                    try:
                        代导入学员=Xueyuan_zongbiao.objects.get(shenfenzheng=row['证件号码'])
                    except ValueError as e:
                        error_messages.append(f": {row['证件号码']}。学员不存在")
                        continue

                    except Xueyuan_zongbiao.DoesNotExist:
                        pass
                if success_count > 0:
                    return HttpResponse(f"成功导入 {success_count} 条记录. 错误信息: {'; '.join(error_messages)}",
                                        status=200)
                else:
                    print(f"错误信息id: {gslssbad}")
                    return HttpResponse(f"没有记录被导入. 错误信息: {'; '.join(error_messages)}", status=400)

            elif pk == 5:
                # 通过文件批量进班
                if 'pljb' in request.POST:
                    if pk !=5:
                        return HttpResponse("请先选择批量进班功能", status=400)

                    pljb = request.POST.get('pljb')
                    file = request.FILES['file']
                    df = pd.read_excel(file)
                    success_count = 0
                    error_messages = []
                    for _, row in df.iterrows():
                        try:
                            xueyuans = Xueyuan_zongbiao.objects.get(shenfenzheng=row['证件号码'])
                            banji = Banji.objects.get(id=pljb)
                            banji.xueyuan.add(xueyuans)
                            success_count += 1
                        except Exception as e:
                            error_message = f"Error processing row {row}: {e}"
                            error_messages.append(error_message)
                    if success_count > 0:
                        return HttpResponse(f"成功导入 {success_count} 条记录. 错误信息: {'; '.join(error_messages)}", status=200)
                    else:
                        return HttpResponse(f"没有记录被导入. 错误信息: {'; '.join(error_messages)}", status=400)
                else:
                    return HttpResponse("检查访问url，没有传入班级信息", status=400)
            else:
                return HttpResponse("功能参数错误", status=400)
        else:
            context['form'] = form  # 如果表单不有效，更新 context 中的表单
    return render(request, 'upload_excel.html', context)

def search_kehu(queryset, search_term, current_user):
    """客户搜索功能"""
    q_objects = Q()

    if 'username' in search_term:
        q_objects.add(Q(username__icontains=search_term['username']), Q.AND)

    if 'tel' in search_term:
        q_objects.add(Q(tel__icontains=search_term['tel']), Q.AND)

    if 'tg_qudao' in search_term:
        tg_qudao_value = search_term['tg_qudao']
        if tg_qudao_value.isdigit():  # 检查是否为数字字符串
            tg_qudao_id = int(tg_qudao_value)  # 转换为整数
            q_objects.add(Q(tg_qudao_id=tg_qudao_id), Q.AND)
        # 如果不是数字字符串，可以选择忽略或添加其他逻辑

    if 'admin_user' in search_term and current_user.is_superuser:
        admin_user_value = search_term['admin_user']
        if admin_user_value.isdigit():  # 同样检查是否为数字字符串
            admin_user_id = int(admin_user_value)  # 转换为整数
            q_objects.add(Q(admin_user_id=admin_user_id), Q.AND)

    queryset = queryset.filter(q_objects)
    return queryset

from KeHu.models import KeHu
def search_xueyuan(queryset, search_term, current_user):
    """客户搜索功能"""
    q_objects = Q()
    # 暂不做逻辑处理
    if 'username' in search_term:
        q_objects.add(Q(kehu__username__icontains=search_term['username']), Q.AND)

    elif 'shenfenzheng' in search_term:
        q_objects.add(Q(shenfenzheng__icontains=search_term['shenfenzheng']), Q.AND)
        q_objects.add(Q(xueyuan_ziliao__zjhm__icontains=search_term['shenfenzheng']), Q.AND)
        # xueyuan_zongbiao = Xueyuan_zongbiao.objects.filter(xueyuan_ziliao__zjhm__icontains=search_term['shenfenzheng'])

    elif 'tel' in search_term:
        q_objects.add(Q(kehu__tel__icontains=search_term['tel']), Q.AND)

    elif 'tg_qudao' in search_term:
        tg_qudao_value = search_term['tg_qudao']
        if tg_qudao_value.isdigit():  # 检查是否为数字字符串
            tg_qudao_id = int(tg_qudao_value)  # 转换为整数
            q_objects.add(Q(kehu__tg_qudao_id=tg_qudao_id), Q.AND)
        # 如果不是数字字符串，可以选择忽略或添加其他逻辑

    elif 'admin_user' in search_term and current_user.is_superuser:
        admin_user_value = search_term['admin_user']
        if admin_user_value.isdigit():  # 同样检查是否为数字字符串
            admin_user_id = int(admin_user_value)  # 转换为整数
            q_objects.add(Q(kehu__admin_user_id=admin_user_id), Q.AND)

    queryset = queryset.filter(q_objects)
    return queryset

